using System;
using Microsoft.Data.SqlClient;
using System.Windows.Forms;

namespace 数据库开发技术实验1
{
    public partial class Form1 : Form
    {
        public Form1()
        {
            InitializeComponent();
        }

        private void Form1_Load(object sender, EventArgs e)
        {
            string connStr = "Data Source=DESKTOP-GU19NI6;Initial Catalog=pubs;Integrated Security=True;TrustServerCertificate=True;";
            using (SqlConnection conn = new SqlConnection(connStr))
            {
                conn.Open();

                string sql = "SELECT DISTINCT state, city FROM authors ORDER BY state, city ;";
                SqlCommand cmd = new SqlCommand(sql, conn);
                SqlDataReader reader = cmd.ExecuteReader();

                string currentState = null;
                TreeNode stateNode = null;

                while (reader.Read())
                {
                    string state = reader["state"].ToString();
                    string city = reader["city"].ToString();

                    if (currentState != state)
                    {
                        stateNode = tvArea.Nodes.Add(state);
                        currentState = state;
                    }
                    stateNode.Nodes.Add(city);
                }
            }

            // 绑定 TreeView 的节点点击事件
            this.tvArea.NodeMouseClick += new TreeNodeMouseClickEventHandler(this.tvArea_NodeMouseClick);
        }

        private void tvArea_NodeMouseClick(object sender, TreeNodeMouseClickEventArgs e)
        {
            // 获取选中节点的城市名称
            string selectedCity = e.Node.Text;

            // 加载数据
            LoadData(selectedCity);
        }

        private void LoadData(string city)
        {
            if (string.IsNullOrEmpty(city))
            {
                MessageBox.Show("请选择一个城市节点！");
                return;
            }

            string connStr = "Data Source=DESKTOP-GU19NI6;Initial Catalog=pubs;Integrated Security=True;TrustServerCertificate=True;";
            using (SqlConnection conn = new SqlConnection(connStr))
            {
                try
                {
                    conn.Open();
                    string sql = $"SELECT au_id, au_lname, au_fname," +
                        $"phone,address,city,state,zip,contract FROM authors WHERE city = '{city}'";

                    SqlCommand cmd = new SqlCommand(sql, conn);
                    SqlDataReader reader = cmd.ExecuteReader();

                    if (!reader.HasRows)
                    {
                        MessageBox.Show("未找到匹配的记录！");
                        dgvAuthors.Rows.Clear();
                        return;
                    }

                    dgvAuthors.Rows.Clear();

                    if (dgvAuthors.Columns.Count == 0)
                    {
                        dgvAuthors.Columns.Add("au_id", "作者ID");
                        dgvAuthors.Columns.Add("au_lname", "姓");
                        dgvAuthors.Columns.Add("au_fname", "名");
                        dgvAuthors.Columns.Add("phone", "电话号码");
                        dgvAuthors.Columns.Add("address", "地址");
                        dgvAuthors.Columns.Add("city", "城市");
                        dgvAuthors.Columns.Add("state", "州");
                        dgvAuthors.Columns.Add("zip", "压缩");
                        dgvAuthors.Columns.Add("contract", "协约");
                    }

                    while (reader.Read())
                    {
                        dgvAuthors.Rows.Add(
                            reader["au_id"].ToString(),
                            reader["au_lname"].ToString(),
                            reader["au_fname"].ToString(),
                            reader["phone"].ToString(),
                            reader["address"].ToString(),
                            reader["city"].ToString(),
                            reader["state"].ToString(),
                            reader["zip"].ToString(),
                            reader["contract"].ToString()
                        );
                    }
                }
                catch (Exception ex)
                {
                    MessageBox.Show($"查询失败: {ex.Message}");
                }
            }
        }

        private void btnQuery_Click(object sender, EventArgs e)
        {
            
            // 获取选中的城市节点
            string city = tvArea.SelectedNode?.Text;
            if (string.IsNullOrEmpty(city))
            {
                MessageBox.Show("请选择一个城市节点！");
                return;
            }

            // 数据库连接字符串
            string connStr = "Data Source=DESKTOP-GU19NI6;Initial Catalog=pubs;Integrated Security=True;TrustServerCertificate=True;";
            using (SqlConnection conn = new SqlConnection(connStr))
            {
                try
                {
                    conn.Open();
                    // 拼接 SQL 查询语句
                    string sql = $"SELECT * FROM authors WHERE city = '{city}'";

                    // 执行查询
                    SqlCommand cmd = new SqlCommand(sql, conn);
                    SqlDataReader reader = cmd.ExecuteReader();

                    // 检查是否有结果
                    if (!reader.HasRows)
                    {
                        MessageBox.Show("未找到匹配的记录！");
                        return;
                    }

                    // 清空 DataGridView 并填充数据
                    dgvAuthors.Rows.Clear();
                    if (dgvAuthors.Columns.Count == 0)
                    {
                        dgvAuthors.Columns.Add("au_id", "作者ID");
                        dgvAuthors.Columns.Add("au_lname", "姓");
                        dgvAuthors.Columns.Add("au_fname", "名");
                        dgvAuthors.Columns.Add("phone", "电话号码");
                        dgvAuthors.Columns.Add("address", "地址");
                        dgvAuthors.Columns.Add("city", "城市");
                        dgvAuthors.Columns.Add("state", "州");
                        dgvAuthors.Columns.Add("zip", "压缩");
                        dgvAuthors.Columns.Add("contract", "协约");
                    }

                    while (reader.Read())
                    {
                        dgvAuthors.Rows.Add(
                            reader["au_id"].ToString(),
                            reader["au_lname"].ToString(),
                            reader["au_fname"].ToString(),
                            reader["phone"].ToString(),
                            reader["address"].ToString(),
                            reader["city"].ToString(),
                            reader["state"].ToString(),
                            reader["zip"].ToString(),
                            reader["contract"].ToString()
                        );
                    }
                }
                catch (Exception ex)
                {
                    MessageBox.Show($"查询失败: {ex.Message}");
                }
            }
        }
    }
}